//用户管理-地址表
//http://localhost:3000/user_addresses
var express = require('express');
var router = express.Router();
// 导入连接数据库key
const pool = require('../MySQLkey/kry');
// 导入工具类
const tools = require('../tool/tool');

// 工具函数：验证地址数据
function validateAddressData(data) {
  const { user_id, receiver_name, receiver_phone, province, city, district, detail_address } = data;

  if (!tools.validateUserId(user_id)) {
    return { valid: false, message: '请提供有效的用户ID' };
  }

  if (!tools.sanitizeString(receiver_name)) {
    return { valid: false, message: '收货人姓名不能为空' };
  }

  if (!tools.validatePhone(receiver_phone)) {
    return { valid: false, message: '请提供有效的收货人电话' };
  }

  if (!tools.sanitizeString(province) || !tools.sanitizeString(city) || !tools.sanitizeString(district)) {
    return { valid: false, message: '省份、城市、区县不能为空' };
  }

  if (!tools.sanitizeString(detail_address)) {
    return { valid: false, message: '详细地址不能为空' };
  }

  return { valid: true, message: '地址数据验证通过' };
}

/* GET 获取用户地址列表 */
router.get('/', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  // 获取查询参数
  const { user_id, page = 1, limit = 10, is_default } = req.query;

  // 验证分页参数
  const pagination = tools.validatePagination(page, limit);

  // 构建查询条件
  let whereClause = '';
  let params = [];

  if (user_id) {
    if (!tools.validateUserId(user_id)) {
      return res.status(400).json({
        success: false,
        error: '请提供有效的用户ID'
      });
    }
    whereClause += ' WHERE user_id = ?';
    params.push(parseInt(user_id));
  }

  if (is_default !== undefined) {
    whereClause += whereClause.includes('WHERE') ? ' AND is_default = ?' : ' WHERE is_default = ?';
    params.push(is_default === 'true' ? 1 : 0);
  }

  // 先获取总数
  const countQuery = 'SELECT COUNT(*) as total FROM `user_addresses`' + whereClause;
  const [countResult] = await connection.execute(countQuery, params);
  const total = countResult[0].total;

  // 执行主查询 - 使用简单的字符串拼接避免参数问题
  const mainQuery = `SELECT * FROM \`user_addresses\`${whereClause} ORDER BY is_default DESC, created_at DESC LIMIT ${pagination.limit} OFFSET ${pagination.offset}`;
  const [results] = await connection.execute(mainQuery, params);

  tools.handleSuccess(res, {
    addresses: results,
    pagination: {
      page: pagination.page,
      limit: pagination.limit,
      total,
      totalPages: Math.ceil(total / pagination.limit)
    }
  }, '地址列表获取成功');

} catch (err) {
  tools.handleError(res, err, '获取地址列表失败');
} finally {
  if (connection) connection.release();
}
});

/* GET 获取用户的默认地址 */
router.get('/user/:user_id/default', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const userId = parseInt(req.params.user_id);

  // 验证用户ID
  if (!tools.validateUserId(userId)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的用户ID'
    });
  }

  // 查询默认地址
  const [results] = await connection.execute(
    'SELECT * FROM `user_addresses` WHERE `user_id` = ? AND `is_default` = 1',
    [userId]
  );

  if (results.length === 0) {
    return res.status(404).json({
      success: false,
      error: '该用户没有设置默认地址'
    });
  }

  tools.handleSuccess(res, results[0], '默认地址获取成功');

} catch (err) {
  tools.handleError(res, err, '获取默认地址失败');
} finally {
  if (connection) connection.release();
}
});

/* GET 根据user_id获取某个用户所有地址列表 */
router.get('/user/:user_id/addresses', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const userId = parseInt(req.params.user_id);

  // 验证用户ID
  if (!tools.validateUserId(userId)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的用户ID'
    });
  }

  // 验证用户是否存在
  const [userExists] = await connection.execute(
    'SELECT id FROM `users` WHERE `id` = ? AND status != 0',
    [userId]
  );

  if (userExists.length === 0) {
    return res.status(404).json({
      success: false,
      error: '用户不存在或已被删除'
    });
  }

  // 查询用户的所有地址
  const [results] = await connection.execute(
    'SELECT * FROM `user_addresses` WHERE `user_id` = ? ORDER BY is_default DESC, created_at DESC',
    [userId]
  );

  tools.handleSuccess(res, {
    userId: userId,
    addresses: results,
    count: results.length
  }, results.length > 0 ? '用户地址列表获取成功' : '该用户暂无地址');

} catch (err) {
  tools.handleError(res, err, '获取用户地址列表失败');
} finally {
  if (connection) connection.release();
}
});

/* GET 根据ID获取单个地址 */
router.get('/:id', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const addressId = parseInt(req.params.id);

  // 验证地址ID
  if (!tools.validateUserId(addressId)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的地址ID'
    });
  }

  // 查询地址信息
  const [results] = await connection.execute(
    'SELECT * FROM `user_addresses` WHERE `id` = ?',
    [addressId]
  );

  if (results.length === 0) {
    return res.status(404).json({
      success: false,
      error: '地址不存在'
    });
  }

  tools.handleSuccess(res, results[0], '地址信息获取成功');

} catch (err) {
  tools.handleError(res, err, '获取地址信息失败');
} finally {
  if (connection) connection.release();
}
});

/* POST 创建新地址 */
router.post('/', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  let {
    user_id,
    receiver_name,
    receiver_phone,
    province,
    city,
    district,
    detail_address,
    postal_code,
    is_default
  } = req.body;

  // 如果没有提供user_id，自动创建新用户
  if (!user_id) {
    // 从地址信息中提取用户信息创建新用户
    const newUserData = {
      nickname: tools.sanitizeString(receiver_name) || '新用户',
      phone: tools.sanitizeString(receiver_phone),
      password: 'default123456', // 默认密码，建议后续让用户修改
      status: 1
    };

    // 验证手机号格式
    if (!tools.validatePhone(newUserData.phone)) {
      return res.status(400).json({
        success: false,
        error: '收货人电话格式不正确，无法创建用户'
      });
    }

    // 检查手机号是否已被注册
    const [existingUser] = await connection.execute(
      'SELECT id FROM `users` WHERE `phone` = ?',
      [newUserData.phone]
    );

    if (existingUser.length > 0) {
      // 如果手机号已存在，使用现有用户ID
      user_id = existingUser[0].id;
      console.log(`使用现有用户ID: ${user_id}`);
    } else {
      // 创建新用户
      const [userResult] = await connection.execute(
        'INSERT INTO `users` (nickname, phone, password, status, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())',
        [newUserData.nickname, newUserData.phone, newUserData.password, newUserData.status]
      );
      user_id = userResult.insertId;
      console.log(`创建新用户ID: ${user_id}`);
    }
  } else {
    // 如果提供了user_id，验证用户是否存在
    const [userExists] = await connection.execute(
      'SELECT id FROM `users` WHERE `id` = ? AND status != 0',
      [user_id]
    );

    if (userExists.length === 0) {
      return res.status(404).json({
        success: false,
        error: '指定的用户不存在或已被删除'
      });
    }
  }

  // 验证地址数据（现在user_id已经确定）
  const addressDataWithUserId = { ...req.body, user_id };
  const validation = validateAddressData(addressDataWithUserId);
  if (!validation.valid) {
    return res.status(400).json({
      success: false,
      error: validation.message
    });
  }

  // 开始事务
  await connection.beginTransaction();

  try {
    // 如果设置为默认地址，先将该用户的其他地址设为非默认
    if (is_default) {
      await connection.execute(
        'UPDATE `user_addresses` SET `is_default` = 0 WHERE `user_id` = ?',
        [user_id]
      );
    }

    // 插入新地址
    const [result] = await connection.execute(
      'INSERT INTO `user_addresses` (user_id, receiver_name, receiver_phone, province, city, district, detail_address, postal_code, is_default, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())',
      [
        user_id,
        tools.sanitizeString(receiver_name),
        tools.sanitizeString(receiver_phone),
        tools.sanitizeString(province),
        tools.sanitizeString(city),
        tools.sanitizeString(district),
        tools.sanitizeString(detail_address),
        tools.sanitizeString(postal_code) || null,
        is_default ? 1 : 0
      ]
    );

    // 提交事务
    await connection.commit();

    // 获取创建的地址信息
    const [newAddress] = await connection.execute(
      'SELECT * FROM `user_addresses` WHERE `id` = ?',
      [result.insertId]
    );

    tools.handleSuccess(res, {
      addressId: result.insertId,
      user_id,
      address: newAddress[0],
      message: user_id ? '地址创建成功' : '新用户和地址创建成功'
    }, user_id ? '地址创建成功' : '新用户和地址创建成功', 201);

  } catch (err) {
    // 回滚事务
    await connection.rollback();
    throw err;
  }

} catch (err) {
  tools.handleError(res, err, '创建地址失败');
} finally {
  if (connection) connection.release();
}
});

/* POST 快速创建用户和地址 */
router.post('/quick-create', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const {
    receiver_name,
    receiver_phone,
    province,
    city,
    district,
    detail_address,
    postal_code,
    is_default = true,
    nickname,
    email
  } = req.body;

  // 验证必填字段
  if (!tools.sanitizeString(receiver_name) || !tools.validatePhone(receiver_phone) ||
      !tools.sanitizeString(province) || !tools.sanitizeString(city) ||
      !tools.sanitizeString(district) || !tools.sanitizeString(detail_address)) {
    return res.status(400).json({
      success: false,
      error: '收货人姓名、电话、省份、城市、区县、详细地址为必填项'
    });
  }

  // 开始事务
  await connection.beginTransaction();

  try {
    let user_id;
    let isNewUser = false;

    // 检查手机号是否已被注册
    const [existingUser] = await connection.execute(
      'SELECT id, nickname FROM `users` WHERE `phone` = ?',
      [receiver_phone]
    );

    if (existingUser.length > 0) {
      // 使用现有用户
      user_id = existingUser[0].id;
      console.log(`使用现有用户: ${existingUser[0].nickname} (ID: ${user_id})`);
    } else {
      // 创建新用户
      isNewUser = true;
      const newUserData = {
        nickname: tools.sanitizeString(nickname) || tools.sanitizeString(receiver_name) || '新用户',
        phone: tools.sanitizeString(receiver_phone),
        email: tools.sanitizeString(email) || null,
        password: 'default123456', // 默认密码
        status: 1
      };

      const [userResult] = await connection.execute(
        'INSERT INTO `users` (nickname, phone, email, password, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW())',
        [newUserData.nickname, newUserData.phone, newUserData.email, newUserData.password, newUserData.status]
      );
      user_id = userResult.insertId;
      console.log(`创建新用户: ${newUserData.nickname} (ID: ${user_id})`);
    }

    // 如果设置为默认地址，先将该用户的其他地址设为非默认
    if (is_default) {
      await connection.execute(
        'UPDATE `user_addresses` SET `is_default` = 0 WHERE `user_id` = ?',
        [user_id]
      );
    }

    // 创建地址
    const [addressResult] = await connection.execute(
      'INSERT INTO `user_addresses` (user_id, receiver_name, receiver_phone, province, city, district, detail_address, postal_code, is_default, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())',
      [
        user_id,
        tools.sanitizeString(receiver_name),
        tools.sanitizeString(receiver_phone),
        tools.sanitizeString(province),
        tools.sanitizeString(city),
        tools.sanitizeString(district),
        tools.sanitizeString(detail_address),
        tools.sanitizeString(postal_code) || null,
        is_default ? 1 : 0
      ]
    );

    // 提交事务
    await connection.commit();

    // 获取创建的地址信息
    const [newAddress] = await connection.execute(
      'SELECT * FROM `user_addresses` WHERE `id` = ?',
      [addressResult.insertId]
    );

    // 获取用户信息
    const [userInfo] = await connection.execute(
      'SELECT id, nickname, phone, email, created_at FROM `users` WHERE `id` = ?',
      [user_id]
    );

    tools.handleSuccess(res, {
      user: userInfo[0],
      address: newAddress[0],
      isNewUser,
      message: isNewUser ? '新用户和地址创建成功' : '地址添加到现有用户成功'
    }, isNewUser ? '新用户和地址创建成功' : '地址添加到现有用户成功', 201);

  } catch (err) {
    // 回滚事务
    await connection.rollback();
    throw err;
  }

} catch (err) {
  tools.handleError(res, err, '创建用户和地址失败');
} finally {
  if (connection) connection.release();
}
});

/* POST 为指定用户创建新地址 */
router.post('/user/:user_id', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const user_id = parseInt(req.params.user_id);
  const {
    receiver_name,
    receiver_phone,
    province,
    city,
    district,
    detail_address,
    postal_code,
    is_default
  } = req.body;

  // 验证用户ID
  if (!tools.validateUserId(user_id)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的用户ID'
    });
  }

  // 验证地址数据（不包含user_id验证，因为从路径获取）
  const addressData = { user_id, ...req.body };
  const validation = validateAddressData(addressData);
  if (!validation.valid) {
    return res.status(400).json({
      success: false,
      error: validation.message
    });
  }

  // 验证用户是否存在
  const [userExists] = await connection.execute(
    'SELECT id FROM `users` WHERE `id` = ? AND status != 0',
    [user_id]
  );

  if (userExists.length === 0) {
    return res.status(404).json({
      success: false,
      error: '用户不存在或已被删除'
    });
  }

  // 开始事务
  await connection.beginTransaction();

  try {
    // 如果设置为默认地址，先将该用户的其他地址设为非默认
    if (is_default) {
      await connection.execute(
        'UPDATE `user_addresses` SET `is_default` = 0 WHERE `user_id` = ?',
        [user_id]
      );
    }

    // 插入新地址
    const [result] = await connection.execute(
      'INSERT INTO `user_addresses` (user_id, receiver_name, receiver_phone, province, city, district, detail_address, postal_code, is_default, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())',
      [
        user_id,
        tools.sanitizeString(receiver_name),
        tools.sanitizeString(receiver_phone),
        tools.sanitizeString(province),
        tools.sanitizeString(city),
        tools.sanitizeString(district),
        tools.sanitizeString(detail_address),
        tools.sanitizeString(postal_code) || null,
        is_default ? 1 : 0
      ]
    );

    // 提交事务
    await connection.commit();

    tools.handleSuccess(res, {
      addressId: result.insertId,
      user_id,
      receiver_name: tools.sanitizeString(receiver_name),
      is_default: is_default ? true : false
    }, '地址创建成功', 201);

  } catch (err) {
    // 回滚事务
    await connection.rollback();
    throw err;
  }

} catch (err) {
  tools.handleError(res, err, '创建地址失败');
} finally {
  if (connection) connection.release();
}
});

/* PUT 更新地址信息 */
router.put('/:id', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const addressId = parseInt(req.params.id);
  const {
    receiver_name,
    receiver_phone,
    province,
    city,
    district,
    detail_address,
    postal_code,
    is_default
  } = req.body;

  // 验证地址ID
  if (!tools.validateUserId(addressId)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的地址ID'
    });
  }

  // 检查地址是否存在
  const [existingAddress] = await connection.execute(
    'SELECT id, user_id FROM `user_addresses` WHERE `id` = ?',
    [addressId]
  );

  if (existingAddress.length === 0) {
    return res.status(404).json({
      success: false,
      error: '地址不存在'
    });
  }

  const userId = existingAddress[0].user_id;

  // 验证更新数据（如果提供了必填字段）
  if (receiver_phone && !tools.validatePhone(receiver_phone)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的收货人电话'
    });
  }

  // 开始事务
  await connection.beginTransaction();

  try {
    // 如果设置为默认地址，先将该用户的其他地址设为非默认
    if (is_default) {
      await connection.execute(
        'UPDATE `user_addresses` SET `is_default` = 0 WHERE `user_id` = ? AND `id` != ?',
        [userId, addressId]
      );
    }

    // 构建更新语句
    let updateFields = [];
    let updateValues = [];

    if (receiver_name !== undefined) {
      updateFields.push('receiver_name = ?');
      updateValues.push(tools.sanitizeString(receiver_name));
    }
    if (receiver_phone !== undefined) {
      updateFields.push('receiver_phone = ?');
      updateValues.push(tools.sanitizeString(receiver_phone));
    }
    if (province !== undefined) {
      updateFields.push('province = ?');
      updateValues.push(tools.sanitizeString(province));
    }
    if (city !== undefined) {
      updateFields.push('city = ?');
      updateValues.push(tools.sanitizeString(city));
    }
    if (district !== undefined) {
      updateFields.push('district = ?');
      updateValues.push(tools.sanitizeString(district));
    }
    if (detail_address !== undefined) {
      updateFields.push('detail_address = ?');
      updateValues.push(tools.sanitizeString(detail_address));
    }
    if (postal_code !== undefined) {
      updateFields.push('postal_code = ?');
      updateValues.push(tools.sanitizeString(postal_code));
    }
    if (is_default !== undefined) {
      updateFields.push('is_default = ?');
      updateValues.push(is_default ? 1 : 0);
    }

    if (updateFields.length === 0) {
      return res.status(400).json({
        success: false,
        error: '没有提供要更新的字段'
      });
    }

    // 添加更新时间
    updateFields.push('updated_at = NOW()');
    updateValues.push(addressId);

    // 执行更新
    const [result] = await connection.execute(
      `UPDATE \`user_addresses\` SET ${updateFields.join(', ')} WHERE \`id\` = ?`,
      updateValues
    );

    // 提交事务
    await connection.commit();

    // 获取更新后的地址信息
    const [updatedAddress] = await connection.execute(
      'SELECT * FROM `user_addresses` WHERE `id` = ?',
      [addressId]
    );

    tools.handleSuccess(res, {
      address: updatedAddress[0],
      affectedRows: result.affectedRows
    }, '地址信息更新成功');

  } catch (err) {
    // 回滚事务
    await connection.rollback();
    throw err;
  }

} catch (err) {
  tools.handleError(res, err, '更新地址信息失败');
} finally {
  if (connection) connection.release();
}
});

/* DELETE 删除地址 */
router.delete('/:id', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const addressId = parseInt(req.params.id);

  // 验证地址ID
  if (!tools.validateUserId(addressId)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的地址ID'
    });
  }

  // 检查地址是否存在
  const [existingAddress] = await connection.execute(
    'SELECT id, user_id, receiver_name, is_default FROM `user_addresses` WHERE `id` = ?',
    [addressId]
  );

  if (existingAddress.length === 0) {
    return res.status(404).json({
      success: false,
      error: '地址不存在'
    });
  }

  const addressInfo = existingAddress[0];

  // 开始事务
  await connection.beginTransaction();

  try {
    // 删除地址
    const [result] = await connection.execute(
      'DELETE FROM `user_addresses` WHERE `id` = ?',
      [addressId]
    );

    // 如果删除的是默认地址，需要设置一个新的默认地址
    if (addressInfo.is_default) {
      const [otherAddresses] = await connection.execute(
        'SELECT id FROM `user_addresses` WHERE `user_id` = ? ORDER BY created_at DESC LIMIT 1',
        [addressInfo.user_id]
      );

      if (otherAddresses.length > 0) {
        await connection.execute(
          'UPDATE `user_addresses` SET `is_default` = 1 WHERE `id` = ?',
          [otherAddresses[0].id]
        );
      }
    }

    // 提交事务
    await connection.commit();

    tools.handleSuccess(res, {
      deletedAddress: {
        id: addressInfo.id,
        receiver_name: addressInfo.receiver_name,
        was_default: addressInfo.is_default === 1
      },
      affectedRows: result.affectedRows
    }, '地址删除成功');

  } catch (err) {
    // 回滚事务
    await connection.rollback();
    throw err;
  }

} catch (err) {
  tools.handleError(res, err, '删除地址失败');
} finally {
  if (connection) connection.release();
}
});

/* POST 设置默认地址 */
router.post('/:id/default', async function(req, res, next) {
let connection;
try {
  connection = await pool.getConnection();

  const addressId = parseInt(req.params.id);

  // 验证地址ID
  if (!tools.validateUserId(addressId)) {
    return res.status(400).json({
      success: false,
      error: '请提供有效的地址ID'
    });
  }

  // 检查地址是否存在
  const [existingAddress] = await connection.execute(
    'SELECT id, user_id, receiver_name FROM `user_addresses` WHERE `id` = ?',
    [addressId]
  );

  if (existingAddress.length === 0) {
    return res.status(404).json({
      success: false,
      error: '地址不存在'
    });
  }

  const userId = existingAddress[0].user_id;

  // 开始事务
  await connection.beginTransaction();

  try {
    // 将该用户的所有地址设为非默认
    await connection.execute(
      'UPDATE `user_addresses` SET `is_default` = 0 WHERE `user_id` = ?',
      [userId]
    );

    // 设置指定地址为默认
    const [result] = await connection.execute(
      'UPDATE `user_addresses` SET `is_default` = 1, `updated_at` = NOW() WHERE `id` = ?',
      [addressId]
    );

    // 提交事务
    await connection.commit();

    tools.handleSuccess(res, {
      addressId,
      receiver_name: existingAddress[0].receiver_name,
      affectedRows: result.affectedRows
    }, '默认地址设置成功');

  } catch (err) {
    // 回滚事务
    await connection.rollback();
    throw err;
  }

} catch (err) {
  tools.handleError(res, err, '设置默认地址失败');
} finally {
  if (connection) connection.release();
}
});

module.exports = router;